﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Xml.Linq;

using MicroDb.Basis;
using MicroDb.Helper;
using MicroDb.Options;

using Microsoft.Extensions.Logging;

using Npgsql;

namespace MicroDb.PostgreSql
{

    public class NpgSqlTableExecute : TableExecute
    {
        private SqlExecute execute;
        private readonly ILogger<TableExecute> logger;

        private readonly Dictionary<string, string> dataTypeMaps;
        public NpgSqlTableExecute(SqlExecute execute, ILogger<TableExecute> logger = null)
        {
            this.execute = execute;
            dataTypeMaps = GetDateTypeMapping();
            this.logger = logger;
        }


        public override void CreateDb(string dbName)
        {
            //创建数据库
            execute.ExecuteNonQuery(CommandType.Text, $"CREATE SCHEMA \"{dbName}\";", null);
        }

        public override void CreateTable(CodeFirstParentInfo tableInfo)
        {
            string ps = string.Join(",\r\n", tableInfo.Children.Select(GetColSql));
            string pk = tableInfo.Attributes.GetDefalutValue("cf-pkey", "");
            var tableName = $"\"{ChangeNameAs(tableInfo.Name)}\"";
            if (!string.IsNullOrEmpty(pk))
            {
                ps += ",\r\n" + pk;
            }
            string sql = $"CREATE TABLE {tableName} ({ps}); \r\n";
            var comments = tableInfo.Children
                .Select(a => new { comment = a.Attributes.GetDefalutValue("summary", "").Trim(), name = ChangeNameAs(a.Name) })
                .Where(a => !string.IsNullOrEmpty(a.comment))
                .ToArray();
            if (comments.Length > 0)
            {
                sql += string.Join("", comments.Select(col => $"COMMENT ON COLUMN {tableName}.\"{col.name}\" IS '{col.comment}' ; \r\n "));
            }
            var summary = tableInfo.Attributes.GetDefalutValue("summary", "");
            if (!string.IsNullOrEmpty(summary))
            {
                sql += $"COMMENT ON TABLE {tableName} IS '{summary.Trim()}'; \r\n";
            }
            string indexs = tableInfo.Attributes.GetDefalutValue("cf-indexs", "");
            if (!string.IsNullOrEmpty(indexs))
            {
                sql += indexs;
            }
            execute.ExecuteNonQuery(CommandType.Text, sql, null);
        }

        private string GetColSql(CodeFirstInfo info)
        {
            return $"\"{ChangeNameAs(info.Name)}\" {info.Attributes["cf-extend"]} ";
        }

        public override void AlterColumns(string dbName, string tableName, CodeFirstInfo[] columns)
        {
            var oldCols = GetColumns(dbName, tableName);
            var newCols = new List<CodeFirstInfo>();
            foreach (var col in columns)
            {
                string colName = ChangeNameAs(col.Name);
                var old = oldCols.FirstOrDefault(a => a.ColumnName == colName);
                if (old == null)
                {
                    newCols.Add(col);
                }
                else
                {
                    if (!col.Attributes["cf-extend"].Contains(old.DbType))
                    {
                        logger.LogInformation($"字段 \"{tableName}\".\"{colName}\" 属性发生变化，请手动修改");
                    }
                }
            }
            if (newCols.Count > 0)
            {
                execute.ExecuteNonQuery(CommandType.Text, string.Join("", newCols.Select(col => $"ALTER TABLE \"{tableName}\" ADD COLUMN {GetColSql(col)}; \r\n" +
                (col.Attributes.ContainsKey("summary") ? $"COMMENT ON COLUMN \"{tableName}\".\"{ChangeNameAs(col.Name)}\" IS '{col.Attributes.GetDefalutValue("summary", "").Trim()}'; \r\n" : ""))), null);
            }
        }

        public override ColumnInfo[] GetColumns(string dbName, string tableName)
        {
            using (var reader = execute.ExecuteReader(CommandType.Text, @"select 
  case WHEN  pk.indisprimary then 'YES' else 'NO' end as col_key,
	i.column_default,
  a.attname as COL_NAME,
	i.identity_generation as extra,
	i.is_nullable as is_nullable,
  d.description column_comment,
i.udt_name as db_type,
COALESCE(i.character_maximum_length,i.datetime_precision,i.numeric_precision) as db_length, 
i.numeric_precision_radix,
i.numeric_scale
from
 information_schema.columns i INNER JOIN
  pg_attribute a on i.table_schema = @schema and i.table_name = @tableName and i.column_name = a.attname
     left join pg_class c  on a.attrelid = c.oid
    left join pg_description d on d.objoid = a.attrelid and
  d.objsubid = a.attnum
  left join pg_index pk on c.oid = pk.indrelid and pk.indkey[0] = a.attnum
where
  c.relnamespace = (SELECT OID FROM pg_namespace WHERE nspname = @schema) and
  a.attnum > 0
   and c.relname = @tableName
order by i.dtd_identifier::int8; ", new Dictionary<string, object>() { { "@schema", dbName }, { "@tableName", tableName } }))
            {
                var list = new List<ColumnInfo>();
                while (reader.Read())
                {
                    var dbLength = reader["db_length"]?.ToString();
                    var numericScale = reader["numeric_scale"]?.ToString();
                    var numericPrecisionRadix = reader["numeric_precision_radix"]?.ToString();
                    var dbType = reader["db_type"].ToString();
                    var defaultValue = reader["column_default"]?.ToString();
                    string columnType = dbType;
                    int.TryParse(dbLength, out int length);
                    if (!string.IsNullOrEmpty(dbLength) && numericPrecisionRadix != "2")
                    {
                        if (numericPrecisionRadix == "10")
                        {
                            columnType += $"({dbLength},{numericScale})";
                        }
                        else
                        {
                            columnType += $"({dbLength})";
                        }
                    }
                    if (string.IsNullOrEmpty(defaultValue))
                    {
                        defaultValue = "";
                    }
                    else
                    {
                        defaultValue = $" DEFAULT {defaultValue}";
                    }
                    if (!dataTypeMaps.TryGetValue(dbType, out string dataType))
                    {
                        dataType = "string";
                    }
                    ColumnInfo columnInfo = new ColumnInfo()
                    {
                        ColumnName = reader["col_name"].ToString(),
                        Comment = reader["column_comment"].ToString(),
                        DbType = columnType,
                        DataType = dataType,
                        DateLength= length,
                        IsNullable = reader["is_nullable"].ToString().Equals("YES", StringComparison.OrdinalIgnoreCase),
                        IsIncrement = reader["extra"].ToString().Equals("ALWAYS", StringComparison.OrdinalIgnoreCase),
                        IsPrimaryKey = reader["col_key"].ToString().Equals("YES", StringComparison.OrdinalIgnoreCase),
                        ColumnKey = reader["col_key"].ToString(),
                    };
                    var dict = new Dictionary<string, string>();
                    dict.Add("cf-extend", $"{columnType}{GetChar(dbType)}{(columnInfo.IsNullable ? "" : " NOT NULL")}{defaultValue} {(columnInfo.IsIncrement ? GetIncrement() : "")}");
                    columnInfo.CodeFist = dict;
                    list.Add(columnInfo);
                }
                return list.ToArray();
            }
        }


        private string GetIncrement()
        {
            return @" GENERATED ALWAYS AS IDENTITY (
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
)";
        }
        private string GetChar(string type)
        {
            if (type.Contains("char") || type.Contains("text"))
            {
                return " COLLATE \"pg_catalog\".\"default\"";
            }
            return "";
        }

        public override string GetCurrentDb(MicroDbOptions microDb)
        {
            return new NpgsqlConnectionStringBuilder(microDb.DbConnectionString).SearchPath;
        }

        public override List<string> GetDbList()
        {
            return execute.ExecuteList<string>(CommandType.Text, "SELECT nspname FROM pg_catalog.pg_namespace;", null);
        }

        public override Dictionary<string, string> GetTableCodeFirst(string dbName, string tableName)
        {
            var dict = new Dictionary<string, string>();
            var list = execute.ExecuteList<IndexInfo>(CommandType.Text, $"select indexname,indexdef from pg_indexes where schemaname='{dbName}' AND	tablename = '{tableName}'", null);
            var indexs = new List<string>();
            foreach (var item in list)
            {
                if (item.Indexname.EndsWith("_pkey"))
                {
                    dict.Add("cf-pkey", $"CONSTRAINT \"{item.Indexname}\" PRIMARY KEY ({string.Join(",", GetIndexPv(item.Indexdef).Split(',').Select(st => $"\"{st.Trim()}\""))})");
                }
                else
                {
                    indexs.Add(item.Indexdef.Replace(dbName + ".", ""));
                }
            }
            if (indexs.Count > 0)
                dict.Add("cf-indexs", string.Join(";\r\n", indexs));
            return dict;
        }

        private string GetIndexPv(string def)
        {
            return def.Substring(def.LastIndexOf("(") + 1).TrimEnd(')');
        }
        public override List<TableInfo> GetTables(string dbName)
        {
            return execute.ExecuteList<TableInfo>(CommandType.Text, $"SELECT  \"table_name\",obj_description(table_name::regclass) as comment FROM information_schema.tables WHERE     table_schema = '{dbName}' AND table_type = 'BASE TABLE'; ", null);
        }

        public class IndexInfo
        {
            public string Indexname { get; set; }

            public string Indexdef { get; set; }
        }
        public override Dictionary<string, string> GetDateTypeMapping()
        {
            return new Dictionary<string, string>
                    {
                        { string.Empty,                  "string" }, // default
                        { "bigint",                      "long" },
                        { "bigserial",                   "long" },
                        { "bit varying",                 "System.Collections.BitArray" },
                        { "bit",                         "System.Collections.BitArray" },
                        { "varbit",                      "System.Collections.BitArray" },
                        { "hstore",                      "System.Collections.Generic.Dictionary<string, string>" },
                        { "bool",                        "bool" },
                        { "boolean",                     "bool" },
                        { "bytea",                       "byte[]" },
                        { "char",                        "char" },
                        { "character varying",           "string" },
                        { "character",                   "string" },
                        { "cid",                         "uint" },
                        { "box",                         "NpgsqlTypes.NpgsqlBox" },
                        { "cidr",                        "NpgsqlTypes.NpgsqlInet" },
                        { "circle",                      "NpgsqlTypes.NpgsqlCircle" },
                        { "line",                        "NpgsqlTypes.NpgsqlLine" },
                        { "lseg",                        "NpgsqlTypes.NpgsqlLSeg" },
                        { "macaddr",                     "NpgsqlTypes.PhysicalAddress" },
                        { "path",                        "NpgsqlTypes.NpgsqlPath" },
                        { "point",                       "NpgsqlTypes.NpgsqlPoint" },
                        { "polygon",                     "NpgsqlTypes.NpgsqlPolygon" },
                        { "tsquery",                     "NpgsqlTypes.NpgsqlTsQuery" },
                        { "tsvector",                    "NpgsqlTypes.NpgsqlTsVector" },
                        { "inet",                        "NpgsqlTypes.NpgsqlInet" },
                        { "geometry",                   string.Empty },
                        { "citext",                      "string" },
                        { "date",                        "DateTime" },
                        { "decimal",                     "decimal" },
                        { "double precision",            "double" },
                        { "float4",                      "float" },
                        { "float8",                      "double" },
                        { "int",                         "int" },
                        { "int2",                        "short" },
                        { "int4",                        "int" },
                        { "int8",                        "long" },
                        { "integer",                     "int" },
                        { "interval",                    "TimeSpan" },
                        { "json",                        "Newtonsoft.Json.Linq.JToken" },
                        { "jsonb",                       "Newtonsoft.Json.Linq.JToken" },
                        { "money",                       "decimal" },
                        { "name",                        "string" },
                        { "numeric",                     "decimal" },
                        { "oid",                         "uint" },
                        { "oidvector",                   "uint[]" },
                        { "real",                        "float" },
                        { "record",                      "object[]" },
                        { "serial",                      "int" },
                        { "serial4",                     "int" },
                        { "serial8",                     "long" },
                        { "smallint",                    "short" },
                        { "text",                        "string" },
                        { "time",                        "TimeSpan" },
                        { "time with time zone",         "DateTimeOffset" },
                        { "time without time zone",      "TimeSpan" },
                        { "timetz",                      "TimeSpan" },
                        { "timestamp",                   "DateTime" },
                        { "timestamp with time zone",    "DateTime" },
                        { "timestamp without time zone", "DateTime" },
                        { "timestamptz",                 "DateTime" },
                        { "uuid",                        "Guid" },
                        { "xid",                         "uint" },
                        { "xml",                         "string" }
                    };
        }

    }
}
